package edu.bw.dao.impl;

import edu.bw.dao.DeptDao;
import edu.bw.dto.SelectDeptByConditionForm;
import edu.bw.dto.UpdateDeptForm;
import edu.bw.pojo.Dept;
import edu.bw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class DeptDaoImpl implements DeptDao {

    QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    @Override
    public List<Map<String, Object>> selectDeptByCondition(SelectDeptByConditionForm selectCondition) {
        //StringBuilder专门用来拼接sql语句
        StringBuilder sql = new StringBuilder("SELECT d.id,d.dept_name AS deptName ,d.tel,d.email ,d.`desc`,COUNT(u.id)AS emps" +
                " FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id=u.dept_id" +
                " AND `status`=1 WHERE 1=1 ");

        //queryParams用来设置？参数值
        List<Object> queryParams=new ArrayList<>();

        if (selectCondition.getDeptName()!=null&& !selectCondition.getDeptName().isEmpty()){
            //此时去拼接模糊查询
            sql.append(" AND d.dept_name LIKE ? ");
            queryParams.add("%"+selectCondition.getDeptName()+"%");

        }

        sql.append("GROUP BY d.id LIMIT ?,?");

        queryParams.add((selectCondition.getPage()-1)*selectCondition.getLength());
        queryParams.add(selectCondition.getLength());

        try {
            return qr.query(sql.toString(),new MapListHandler(),queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Long selectDeptByConditionCount(SelectDeptByConditionForm selectCondition) {
        StringBuilder sql = new StringBuilder("SELECT COUNT( * ) " +
                "FROM(SELECT d.id,d.dept_name AS deptName,d.tel,d.email,d.`desc`,COUNT( u.id ) AS emps" +
                " FROM bw_zzy_dept d LEFT JOIN bw_zzy_users u ON d.id = u.dept_id AND `status` = 1 ");

        ArrayList<Object> queryParams = new ArrayList<>();

        if (selectCondition.getDeptName()!=null&&!selectCondition.getDeptName().isEmpty()){
            //此时去拼接部门的模糊查询
            sql.append("AND d.dept_name LIKE ?");
            queryParams.add("%"+selectCondition.getDeptName()+"%");
        }
        sql.append(" GROUP BY d.id ) AS emp ");
        try {
            return qr.query(sql.toString(),new ScalarHandler<Long>(),queryParams.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int inset(Dept dept) {
        try {
            return qr.update("INSERT INTO bw_zzy_dept SET dept_name =? ,tel=?,email =? , `desc`=?",dept.getDeptName(),dept.getTel()
            ,dept.getEmail(),dept.getDesc());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<Map<String, Object>> selectById(Integer id) {
        try {
            return qr.query("SELECT id ,dept_name AS deptName ,tel,email,`desc` FROM bw_zzy_dept WHERE id=? ",new MapListHandler(),id);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer updateDept(UpdateDeptForm updateDeptForm) {

        try {
            return qr.update("UPDATE bw_zzy_dept  SET dept_name=? ,tel=?,email=?,`desc`=? WHERE id=?",
                    updateDeptForm.getDeptName(),
                    updateDeptForm.getTel(),
                    updateDeptForm.getEmail(),
                    updateDeptForm.getDesc(),
                    updateDeptForm.getId());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public int deleteDeptByIds(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("DELETE FROM bw_zzy_dept WHERE id IN ( ")
                .append(ids.stream().map(id->"?").collect(Collectors.joining(",")))
                .append(")");
        try {
            return  qr.update(sql.toString(),ids.toArray());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public boolean selectCanDelete(List<Integer> ids) {
        StringBuilder sql = new StringBuilder("SELECT IF(SUM(temp.users),FALSE,TRUE) AS result " +
                "FROM (SELECT COUNT(u.id) AS users FROM bw_zzy_dept d INNER JOIN bw_zzy_users u ON d.id=u.dept_id WHERE d.id IN ( ")
                .append(ids.stream().map(id->"?").collect(Collectors.joining(",")))
                .append(") GROUP BY d.id) temp");

        try {
            Long result = qr.query(sql.toString(), new ScalarHandler<Long>(), ids.toArray());
            return result==1? true:false;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public List<Map<String, Object>> selectAllDept() {
        try {
            return qr.query("SELECT id, dept_name AS deptName\n" +
                    "FROM bw_zzy_dept\n" +
                    "ORDER BY id",new MapListHandler());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
